﻿CREATE PROCEDURE [dbo].[proc_Customer_GetEmployeeList_Search]
(
	@UserName nvarchar(50),
	@Customer nvarchar(100),
	@StartIndex int,
	@EndIndex int
)
As
Begin
	Declare @companyid int,@Days int,@DepartmentId int,@cSql nvarchar(4000)

	EXEC @companyid=proc_Company_GetID @UserName,@companyid	--单位ID
	set @DepartmentId = (Select DepartmentId From aspnet_Users Where UserName=@UserName)--部门Id
	--set @DepartmentId = (Select DepartmentId From Employee Where UserName=@UserName)--部门Id
	Set @Days = (Select ProtectDays From Department Where id=@DepartmentId)--系统保护天数

Set @cSql='
	;WITH list As (Select Top 50 ROW_NUMBER() OVER (Order by ProtectId ASC ,UpTime ASC) AS Row,
		Id,
		delFlag,
		CustomerId,
		CustomerName,
		Project,
		ProtectId,
		ProtectDate As PDate,
		CreateDate,SourceId,IndustryOneId,TypeId,ProtectName
		From Customer
		Where Companyid='+Convert(varchar(10),@companyid)+' And delflag=0 And Audit=1 And CustomerName Like ''%'+@Customer+'%''
	)

	Select *,
		IsNull(('+Convert(varchar(10),@Days)+'-DateDiff(day,IsNull(PDate,GetDate()),GetDate())),0) As Countdown,
		IsNull(PDate,''1900-01-01'') As ProtectDate,
		isnull((select Ename from employee where employee.username=ProtectName),'''') ProtectNameEName,
		IsNull((Select Title From Customer_Source Where Id=list.SourceId),''...'') As Source,
		IsNull((Select Title From Customer_Protect Where Id=list.ProtectId),''...'') As Protect,
		IsNull((Select Title From Customer_Industry Where Id=list.IndustryOneId),''...'') As IndustryOne,
		IsNull((Select Title From Customer_Type Where Id=list.TypeId),''...'') As Type,
		(Select Count(0) From list) As RecordCount
	From list
	Where Row Between ' + Convert(varchar(10),@StartIndex) + ' And ' + Convert(varchar(10),@EndIndex) + ' Order By Row
'
	Execute(@cSql)
End
